package com.gaozhaoyang.dbutils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
/*
 *   dbutils 有事务操作
 *      转账案例
 *    多个sql语句在执行的过程中共用一个connection连接对象共属于一个事务
 * */
public class Dbutils_Demo02 {
    @Test
    public void account() {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        //创建无事务的可执行对象
        QueryRunner qr = new QueryRunner();

        String sql = "update account set money=money-? where name =?";
        Connection con = null;
        try {
            con = dataSource.getConnection();
            //开启事务
            con.setAutoCommit(false);

            int row = qr.update(con, sql, 1500, "pgone");
            if (row > 0) {
                System.out.println("转出成功");
            } else {
                System.out.println("转出失败");
            }


            sql = "update account set money =money+? where name =?";

            row= qr.update(con, sql, 1500, "lxl");

            if (row > 0) {
                System.out.println("收钱成功");
            } else {
                System.out.println("收钱失败");
            }
            //提交事务
            con.commit();
        } catch (SQLException e) {
            try {
                //回滚事务
                con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}
